GENERAL SQL FILE FOR ANY APEX APPLICATION
================================================
(This file contains fully general SQL commands...)

----------------------------------------
-- 1. DROP OLD TABLES 
----------------------------------------
BEGIN EXECUTE IMMEDIATE 'DROP TABLE audit_log PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE children PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE parents PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE materials PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END;
/

----------------------------------------
-- 2. SCHEMA CREATION
----------------------------------------
CREATE TABLE parents (
  parent_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  parent_name VARCHAR2(100) NOT NULL,
  age NUMBER CHECK (age BETWEEN 18 AND 120),
  gender VARCHAR2(20) CHECK (gender IN ('Male','Female','Other')),
  mobile VARCHAR2(20) UNIQUE,
  email VARCHAR2(200),
  num_children NUMBER DEFAULT 0 CHECK (num_children >= 0),
  consent_signed CHAR(1) DEFAULT 'N' CHECK (consent_signed IN ('Y','N')),
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE TABLE children (
  child_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  parent_id NUMBER NOT NULL,
  child_name VARCHAR2(100) NOT NULL,
  child_age NUMBER CHECK (child_age BETWEEN 0 AND 18),
  child_category VARCHAR2(100),
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
  CONSTRAINT fk_children_parent FOREIGN KEY(parent_id) REFERENCES parents(parent_id) ON DELETE CASCADE
);

CREATE TABLE materials (
  material_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  material_name VARCHAR2(100) NOT NULL,
  texture VARCHAR2(50),
  color VARCHAR2(50),
  source VARCHAR2(50) CHECK (source IN ('Organic','Recycled','Synthetic','Natural','Mixed')),
  certificate VARCHAR2(100),
  non_toxic CHAR(1) DEFAULT 'Y' CHECK (non_toxic IN ('Y','N')),
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE TABLE audit_log (
  audit_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  ref_id NUMBER,
  ref_type VARCHAR2(30),
  details VARCHAR2(4000),
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

----------------------------------------
-- 3. TRIGGER
----------------------------------------
CREATE OR REPLACE TRIGGER trg_child_after_insert
AFTER INSERT ON children
FOR EACH ROW
DECLARE
  v_consent CHAR(1);
BEGIN
  SELECT consent_signed INTO v_consent FROM parents WHERE parent_id = :NEW.parent_id;
  IF v_consent <> 'Y' THEN
    RAISE_APPLICATION_ERROR(-20001, 'Consent required before adding child.');
  END IF;

  INSERT INTO audit_log (ref_id, ref_type, details)
  VALUES (:NEW.child_id, 'CHILD',
          'Child added: ' || :NEW.child_name || ', Age: ' || :NEW.child_age);
END;
/
SHOW ERRORS;

----------------------------------------
-- 4. PROCEDURE
----------------------------------------
CREATE OR REPLACE PROCEDURE pr_update_child_count(p_parent_id NUMBER) IS
  v_total NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_total FROM children WHERE parent_id = p_parent_id;
  UPDATE parents SET num_children = v_total WHERE parent_id = p_parent_id;
END;
/
SHOW ERRORS;

----------------------------------------
-- 5. WINDOW FUNCTIONS
----------------------------------------
SELECT parent_id, parent_name, num_children,
       RANK() OVER (ORDER BY num_children DESC) AS rank_no
FROM parents;

SELECT parent_id, child_name, child_age,
       ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY child_age) AS rn,
       LAG(child_age) OVER (PARTITION BY parent_id ORDER BY child_age) AS prev_age,
       LEAD(child_age) OVER (PARTITION BY parent_id ORDER BY child_age) AS next_age
FROM children;

----------------------------------------
-- 6. MODEL CLAUSE
----------------------------------------
WITH age_counts AS (
  SELECT child_age AS age, COUNT(*) cnt FROM children GROUP BY child_age
)
SELECT age, cnt
FROM (
  SELECT LEVEL - 1 AS age, 0 AS cnt FROM dual CONNECT BY LEVEL <= 19
)
MODEL
  DIMENSION BY (age)
  MEASURES (cnt)
  RULES (cnt[ANY] = NVL((SELECT ac.cnt FROM age_counts ac WHERE ac.age = age), 0))
ORDER BY age;

----------------------------------------
-- 7. REPORT QUERIES
----------------------------------------
SELECT * FROM parents ORDER BY parent_name;
SELECT * FROM children ORDER BY child_age, child_name;
SELECT * FROM materials ORDER BY material_name;
SELECT * FROM audit_log ORDER BY created_at DESC;

----------------------------------------
-- 8. CHART QUERIES
----------------------------------------
SELECT child_category AS label, COUNT(*) AS value FROM children GROUP BY child_category;
SELECT gender AS label, COUNT(*) AS value FROM parents GROUP BY gender;
SELECT child_age AS label, COUNT(*) AS value FROM children GROUP BY child_age ORDER BY child_age;

----------------------------------------
-- 9. REGEX VALIDATION (FOR APEX)
----------------------------------------
-- Name: ^[A-Za-z][A-Za-z .'-]{1,99}$
-- Mobile: ^(\+?\d{1,3}[- ]?)?\d{10}$
-- Email: ^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$
-- DOB: ^\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])$
-- Age: ^[0-9]{1,3}$

